<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>openGauss Blog  | openGauss行存与列存</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="shortcut icon" href="https://xzx666.gitee.io/lookeng/img/favicon.ico" type="image/x-icon" />
    <link rel="apple-touch-icon" href="https://xzx666.gitee.io/lookeng/img/apple-touch-icon.png" />

    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
    
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/commen.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/blog.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/mobile.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/pagination.css?t=1607593672000">
    
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?34e9f585f29581007941aa1698181871";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
</head>
<body>
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src='//unpkg.com/valine/dist/Valine.min.js'></script>






<div class="container">
    <div class="columns blog-detail">
        <div class="post_detail">
            <div class="is-child box">
                <div class="breadCrumb"><a href="\zh\">博客/</a></div>
                <div class="blog-detail-header">
                    <h1>openGauss行存与列存</h1>
                    <div class="blog-detail-prop">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-date.png">
                        <span class="article_right_date">2020-11-17</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-author.png">
                        <span class="article_right_author">高云龙</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-view.png">
                        <span id="/zh/post/gaoyunlong/opengauss%E8%A1%8C%E5%AD%98%E4%B8%8E%E5%88%97%E5%AD%98/" class="leancloud_visitors" data-flag-title="openGauss行存与列存">
                            <i class="leancloud-visitors-count"></i>                     
                        </span>
                    </div>
                    <div class="blog-detail-tags">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-tag.png">
                        
                        <a href="https://xzx666.gitee.io/lookeng/zh/tags/opengauss%E8%A1%8C%E5%AD%98%E4%B8%8E%E5%88%97%E5%AD%98">openGauss行存与列存</a>
                        
                    </div>
                </div>
                <div class="content blog-content">
                    

<h1 id="opengauss行存与列存-a-name-zh-cn-topic-0291959499-a">openGauss行存与列存<a name="ZH-CN_TOPIC_0291959499"></a></h1>

<h2 id="列存表限制-a-name-section46133367719-a">列存表限制<a name="section46133367719"></a></h2>

<ul>
<li>列存表不支持数组。</li>
<li>列存表的数量建议不超过1000个。</li>
<li>列存表的表级约束只支持PARTIAL CLUSTER KEY，不支持主外键等表级约束。</li>
<li>列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。</li>
<li>列存表不支持alter命令修改字段约束。</li>
<li>列存表支持delta表，受参数enable_delta_store 控制是否开启，受参数deltarow_threshold控制进入delta表的阀值。</li>
</ul>

<h2 id="列存相关参数-a-name-section176073412819-a">列存相关参数<a name="section176073412819"></a></h2>

<ul>
<li><p>cstore_buffers</p>

<p>列存所使用的共享缓冲区的大小，默认值：32768KB。</p></li>

<li><p>partition_mem_batch</p>

<p>指定缓存个数，为了优化对列存分区表的批量插入，在批量插入过程中会对数据进行缓存后再批量写盘。默认值：256 。</p></li>

<li><p>partition_max_cache_size</p>

<p>指定数据缓存区大小，为了优化对列存分区表的批量插入，在批量插入过程中会对数据进行缓存后再批量写盘。默认值：2GB。</p></li>

<li><p>enable_delta_store</p>

<p>为了增强列存单条数据导入的性能和解决磁盘冗余问题，是否需要开启列存delta表功能，与参数DELTAROW_THRESHOLD 配合使用。默认值：off。</p></li>
</ul>

<h2 id="建表语法-a-name-section6216163918816-a">建表语法<a name="section6216163918816"></a></h2>

<p>openGauss 创建普通表默认是未压缩的行存表。</p>

<pre><code>mydb=# \dt
No relations found.
mydb=# create table test_t(id serial primary key ,col1 varchar(8),col2 decimal(6,2),create_time timestamptz not null default now());
NOTICE:  CREATE TABLE will create implicit sequence &quot;test_t_id_seq&quot; for serial column &quot;test_t.id&quot;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index &quot;test_t_pkey&quot; for table &quot;test_t&quot;
CREATE TABLE
mydb=# \dt+
                                     List of relations
 Schema |  Name  | Type  | Owner |  Size   |             Storage              | Description
--------+--------+-------+-------+---------+----------------------------------+-------------
 public | test_t | table | omm   | 0 bytes | {orientation=row,compression=no} |
(1 row)

mydb=# 
</code></pre>

<p>创建列存表，需要指定<strong>orientation=column</strong>，默认压缩级别是low。</p>

<pre><code>mydb=# create table column_t(id serial,col1 varchar(8),col2 decimal(6,2),create_time timestamptz not null default now()) with (orientation=column );
NOTICE:  CREATE TABLE will create implicit sequence &quot;column_t_id_seq&quot; for serial column &quot;column_t.id&quot;
CREATE TABLE
mydb=# \dt+
                                        List of relations
 Schema |   Name   | Type  | Owner |  Size   |               Storage                | Description
--------+----------+-------+-------+---------+--------------------------------------+-------------
 public | column_t | table | omm   | 16 kB   | {orientation=column,compression=low} |
 public | test_t   | table | omm   | 0 bytes | {orientation=row,compression=no}     |
(2 rows)

mydb=#mydb=# \d+ column_t
                                                        Table &quot;public.column_t&quot;
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Has OIDs: no
Options: orientation=column, compression=low
</code></pre>

<p>列存表添加局部聚簇存储列。</p>

<pre><code>mydb=# \d+ column_t
                                                        Table &quot;public.column_t&quot;
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Has OIDs: no
Options: orientation=column, compression=low

mydb=# alter table column_t add PARTIAL CLUSTER KEY(id);
ALTER TABLE
mydb=# \d+ column_t
                                                        Table &quot;public.column_t&quot;
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_t_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Partial Cluster :
    &quot;column_t_cluster&quot; PARTIAL CLUSTER KEY (id)
Has OIDs: no
Options: orientation=column, compression=low

mydb=#
</code></pre>

<p>直接创建带局部聚簇存储的列存表。</p>

<pre><code>NOTICE:  CREATE TABLE will create implicit sequence &quot;column_c_id_seq&quot; for serial column &quot;column_c.id&quot;
CREATE TABLE
mydb=# \d+ column_c
                                                        Table &quot;public.column_c&quot;
   Column    |           Type           |                       Modifiers                       | Storage  | Stats target | Description
-------------+--------------------------+-------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('column_c_id_seq'::regclass) | plain    |              |
 col1        | character varying(8)     |                                                       | extended |              |
 col2        | numeric(6,2)             |                                                       | main     |              |
 create_time | timestamp with time zone | not null default now()                                | plain    |              |
Partial Cluster :
    &quot;column_c_cluster&quot; PARTIAL CLUSTER KEY (id)
Has OIDs: no
Options: orientation=column, compression=low

mydb=#
</code></pre>

<h2 id="列存与行存对比-a-name-section2310652888-a">列存与行存对比<a name="section2310652888"></a></h2>

<p><strong>磁盘使用空间</strong></p>

<ul>
<li>列存表默认大小16K，low压缩级别。</li>
<li>行存表默认大小0bytes，非压缩级别。</li>

<li><p>分别向两个表中插入100万条数据，占用磁盘大小对比。</p>

<pre><code>mydb=# \dt+
                                      List of relations
Schema |   Name    | Type  | Owner |  Size   |                 Storage                 | Description
--------+-----------+-------+-------+---------+-----------------------------------------+-------------
public | column_t  | table | omm   | 16 kB   | {orientation=column,compression=low}    |
public | column_th | table | omm   | 16 kB   | {orientation=column,compression=high}   |
public | column_tm | table | omm   | 16 kB   | {orientation=column,compression=middle} |
public | row_tc    | table | omm   | 0 bytes | {orientation=row,compression=yes}       |
public | test_t    | table | omm   | 0 bytes | {orientation=row,compression=no}        |
(5 rows)

mydb=# insert into column_t select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 11328.880 ms
mydb=# insert into column_th select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 10188.634 ms
mydb=# insert into column_tm select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 9802.739 ms
mydb=# insert into test_t select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 17404.945 ms
mydb=# insert into row_tc select generate_series(1,1000000),left(md5(random()::text),8),random()::numeric(6,2);
INSERT 0 1000000
Time: 12394.866 ms
mydb=# \dt+
                                       List of relations
Schema |   Name    | Type  | Owner |   Size   |                 Storage                 | Description
--------+-----------+-------+-------+----------+-----------------------------------------+-------------
public | column_t  | table | omm   | 12 MB    | {orientation=column,compression=low}    |
public | column_th | table | omm   | 8304 kB  | {orientation=column,compression=high}   |
public | column_tm | table | omm   | 10168 kB | {orientation=column,compression=middle} |
public | row_tc    | table | omm   | 58 MB    | {orientation=row,compression=yes}       |
public | test_t    | table | omm   | 58 MB    | {orientation=row,compression=no}        |
(5 rows)

mydb=#
</code></pre></li>

<li><p>列存表开启的压缩级别越高。</p></li>

<li><p>占用磁盘空间越少行存表开启压缩后，磁盘空间大小占比减少不明显。</p></li>

<li><p>列存表占用磁盘空间比行存表占用磁盘空间少近6倍。</p></li>
</ul>

<p><strong>DML对比</strong></p>

<p>查找单列</p>

<pre><code>---
---按范围查找，列存比行存快近20倍
---
mydb=# select col1 from test_t where id&gt;=100010 and id&lt;100020;
   col1
----------
 4257a3f3
 3d397284
 64343438
 6eb7bdb7
 d1c9073d
 6aeb037c
 1d424974
 223235ab
 329de235
 2f02adc1
(10 rows)

Time: 77.341 ms
mydb=# select col1 from column_t where id&gt;=100010 and id&lt;100020;
   col1
----------
 d4837c30
 87a46f7a
 2f42a9c9
 4481c793
 68800204
 613b9205
 9d8f4a0a
 5cc4ff9e
 f948cd10
 f2775cee
(10 rows)

Time: 3.884 ms

---
---随机查找，列存比行存快近35倍
---

mydb=# select col1 from test_t limit 10;
   col1
----------
 c2780d93
 294be14d
 4e53b761
 2c10f8a2
 ae776743
 7d683c66
 b3b40054
 7e56edf9
 a7b7336e
 ea3d47d9
(10 rows)

Time: 249.887 ms
mydb=# select col1 from column_t limit 10;
   col1
----------
 a745d77b
 4b6df494
 76fed9c1
 70c9664d
 3384de8a
 4158f3bf
 5d1c3b9f
 341876bb
 f396f4ed
 abfd78bb
(10 rows)

Time: 7.738 ms
</code></pre>

<p>select *</p>

<pre><code>---
---行存比列存查询快30%
---
mydb=# select * from test_t limit 10;
 id |   col1   | col2 |          create_time
----+----------+------+-------------------------------
  1 | c2780d93 |  .37 | 2020-10-26 14:27:33.304108+08
  2 | 294be14d |  .57 | 2020-10-26 14:27:33.304108+08
  3 | 4e53b761 |  .98 | 2020-10-26 14:27:33.304108+08
  4 | 2c10f8a2 |  .27 | 2020-10-26 14:27:33.304108+08
  5 | ae776743 |  .97 | 2020-10-26 14:27:33.304108+08
  6 | 7d683c66 |  .58 | 2020-10-26 14:27:33.304108+08
  7 | b3b40054 |  .44 | 2020-10-26 14:27:33.304108+08
  8 | 7e56edf9 |  .43 | 2020-10-26 14:27:33.304108+08
  9 | a7b7336e |  .31 | 2020-10-26 14:27:33.304108+08
 10 | ea3d47d9 |  .42 | 2020-10-26 14:27:33.304108+08
(10 rows)

Time: 6.822 ms

mydb=# select * from column_t limit 10;
 id |   col1   | col2 |          create_time
----+----------+------+-------------------------------
  1 | a745d77b |  .33 | 2020-10-26 14:28:20.633253+08
  2 | 4b6df494 |  .42 | 2020-10-26 14:28:20.633253+08
  3 | 76fed9c1 |  .73 | 2020-10-26 14:28:20.633253+08
  4 | 70c9664d |  .74 | 2020-10-26 14:28:20.633253+08
  5 | 3384de8a |  .48 | 2020-10-26 14:28:20.633253+08
  6 | 4158f3bf |  .59 | 2020-10-26 14:28:20.633253+08
  7 | 5d1c3b9f |  .63 | 2020-10-26 14:28:20.633253+08
  8 | 341876bb |  .97 | 2020-10-26 14:28:20.633253+08
  9 | f396f4ed |  .73 | 2020-10-26 14:28:20.633253+08
 10 | abfd78bb |  .30 | 2020-10-26 14:28:20.633253+08
(10 rows)

Time: 9.982 ms
</code></pre>

<p>update</p>

<pre><code>---
---直接更新一个字段，列存比行存快近7倍
---
mydb=# update test_t set col1=col1;
UPDATE 1000000
Time: 19779.978 ms
mydb=# update column_t set col1=col1;
UPDATE 1000000
Time: 2702.339 ms
</code></pre>

<h2 id="结论-a-name-section46241214917-a">结论<a name="section46241214917"></a></h2>

<ol>
<li>列存表比行存表在磁盘空间占用上节省近6倍。</li>
<li>查询指定字段，列存表比行存表快约20-35倍。</li>
<li>select * 的方式，列存表比行存表慢30%。</li>
<li>默认压缩方式批量导入数据，列存表比行存表快40%。</li>
</ol>

                </div>
            </div>
            <div class="box" style="margin-top:15px;">
                <div style="font-size:14px;color:gray"><strong>【免责声明】</strong>本文仅代表作者本人观点，与本网站无关。本网站对文中陈述、观点判断保持中立，不对所包含内容的准确性、可靠性或完整性提供任何明示或暗示的保证。本文仅供读者参考，由此产生的所有法律责任均由读者本人承担。</div>
            </div>
            <div class="post-comment">
                
                  
  <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/custom.css" />
  <div id="vcomments" lang="zh-cn" mess="说点什么吧..."></div>

  <script type="text/javascript">
    const placeholder = document.getElementById("vcomments").getAttribute("mess"),
          lang = document.getElementById("vcomments").getAttribute("lang");
    const  langs = lang == "zh-cn" ? "zh-cn" : "en";
    new Valine({
        el: '#vcomments' ,
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm', 
        placeholder: placeholder,
        visitor: true,
		    meta: ['nick','mail','link'],
        lang: langs
    });
  </script>
            </div>
        </div>
    </div>
</div>
<script>
    $(function (){
        var query = new AV.Query('Counter');
        query.equalTo('url', decodeURIComponent('\/zh\/post\/gaoyunlong\/opengauss%E8%A1%8C%E5%AD%98%E4%B8%8E%E5%88%97%E5%AD%98\/'));
        query.find().then(function (data) {
            if(data.length && (data[0].attributes.author === 'openGauss')){
                var counter = AV.Object.createWithoutData('Counter', data[0].id);
                counter.set('author', '高云龙');
                counter.save();
            }
        })
    })
</script>







<input id="iframeUrl" type="text" style="display: none;" value=https://xzx666.gitee.io/>




<script defer src="https://xzx666.gitee.io/lookengjs/all.js"></script>
<script src="https://xzx666.gitee.io/lookengjs/flexible.js"></script>
<script>
  const hash = window.location.search,
        pageurl = window.location.href;
  var langss = document.querySelector("html").lang === "zh-cn" ? "zh" : "en";
  console.log("hash:",hash,"pageurl:",pageurl,"ttr",pageurl.split(langss + "/")[1])

  if(!document.getElementById("vcomments")) {
    new Valine({
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm',
        visitor: true,
		    meta: ['nick','mail','link']
    });
  }
 
  
    


    const url = document.getElementById("iframeUrl").value;

    function observe (el, options, callback) {
      var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver
      var observer = new MutationObserver(callback)
      observer.observe(el, options)
    }

    var options = {
        childList: true,
        subtree: true,
        characterData: true
      }
    observe(document.body, options, (records, instance) => {
    const height = document.body.scrollHeight;
    parent.postMessage(height,url);
    })
    if(pageurl.split(langss + "/")[1]) parent.postMessage(pageurl.split(langss + "/")[1],url);
    

    if(document.querySelector("#notFound")) parent.postMessage("我404了",url);

</script>
</body>
</html>
